CREATE OR REPLACE FUNCTION message_insert() RETURNS trigger AS $message_insert$
DECLARE
  p ltree;
  tmp ltree;
    BEGIN
        tmp:= text2ltree(to_char(NEW.message_id,'FM999999'));
        SELECT path INTO p FROM tree_message WHERE message_id=NEW.parent_id;
        IF FOUND THEN
          p:= p || tmp;
        ELSE
          p:= tmp;
        END IF;
        
        UPDATE tree_message SET path = p WHERE message_id=NEW.message_id;
        UPDATE "user" SET msg_count=msg_count+1 WHERE user_id=NEW.user_id;
        RETURN NEW;
    END;
$message_insert$ LANGUAGE plpgsql;
COMMENT ON FUNCTION message_insert() IS 'sets path for inserted tree_message';

CREATE TRIGGER m_insert AFTER INSERT ON tree_message
    FOR EACH ROW EXECUTE PROCEDURE message_insert();